const { query } = require('./db.js')

const q = `select * from loan `, orderBy = `order by create_time desc `, limit = `limit ?,?`, statusInStr = 'status in (?,?) ',
qCount = 'select count(*) as rows from loan '

exports.findLoanByLimit = (start, size) => query(q + orderBy + limit, [start, size])

exports.findLoanByLimitAndName = (name, start, size) => query(q + `where name like ? ` + orderBy + limit, [`%${name}%`, start, size])

exports.getLoanCountByName = (name) => query(`select count(*) as rows from loan where name like ?`, [`%${name}%`])

exports.getLoanCount = () => query(`select count(*) as rows from loan`)

exports.insertLoan = (obj) => {
  let objKeys = Object.keys(obj)
  // values 要使用？占位，我试了直接把值填充不行；这里要return
  return query(`insert into loan (${objKeys.join(',')}) values (${new Array(objKeys.length).fill('?').join(',')})`, Object.values(obj))
}

exports.updateLoanByfields = (obj, id) => {
  let objKeys = Object.keys(obj);
  let valueKeys = Object.values(obj);
  return query(`update loan set update_time=now(), ${objKeys.join('=?,') + '=?'} where id = ?`, [...valueKeys, id])
}

exports.deleteLoanById = id => query(`delete from loan where id = ?`, [id])

exports.findLoanById = id => query(`select * from loan where id = ?`, [id])

exports.findLoanByLimitAndFields = (start, size, fields) => {
  const keys = Object.keys(fields)
  const values = Object.values(fields)
  return query(`select * from loan where ${keys.join('= ? and ') + '=?'} limit ?,?`, [...values, start, size])
}

exports.countLoanByFields = (fields) => {
  const keys = Object.keys(fields)
  const values = Object.values(fields)
  return query(`select count(*) as rows from loan where ${keys.join('= ? and ') + '=?'}`, values)
}

exports.findLoanByLimitNameStatusIn = (start, size, name, in1, in2) => query(q + `where name like ? and ` + statusInStr + orderBy + limit, [name, in1, in2, start, size])
exports.countLoanByNameStatusIn = (name, in1, in2) => query(qCount + `where name like ? and ` + statusInStr, [name, in1, in2])
exports.findLoanByLimitStatusIn = (start, size, in1, in2) => query(q + `where ` + statusInStr + orderBy + limit, [in1, in2, start, size])
exports.countLoanByStatusIn = (in1, in2) => query(qCount + 'where ' + statusInStr, [in1, in2])